# -*- coding: utf-8 -*-
# @Time    : 2024/1/16 21:30
# @Author  : micah
# @File    : 6.使用python获取腾讯招聘数据并保存到mysql中.py
# @Software: PyCharm

"""
pymysql的使用:
    1.创建一个链接对象
    2.在指定的数据库中创建表
    3.将对应的数据写入到表中
"""

import pymysql
import requests


class TxWork:
    def __init__(self):
        self.url = 'https://careers.tencent.com/tencentcareer/api/post/Query?timestamp=1705411940199&countryId=&cityId=&bgIds=&productId=&categoryId=&parentCategoryId=&attrId=1&keyword=python&pageIndex={}&pageSize=10&language=zh-cn&area=cn'
        self.headers = {
            'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/120.0.0.0 Safari/537.36'
        }

        # 创建数据库链接
        self.db = pymysql.connect(host='localhost', user='root', password='123456', db='py_spider')
        # 游标
        self.cursor = self.db.cursor()

    def __del__(self):
        print('程序即将退出, 准备关闭数据库链接...')
        self.cursor.close()
        self.db.close()

    # 获取数据
    # 当前函数需要使用生成器的方式完成数据返回
    # 因为使用return的方式需要将所有的页面数据全部获取到之后才能返回
    # 占内存
    def get_work_info(self):
        for page in range(1, 41):
            response = requests.get(self.url.format(page), headers=self.headers).json()
            print(f'正在抓取第{page}页')
            work_list = response['Data']['Posts']
            yield work_list  # 抓取一页, 返回一页的数据, 而不是所有的数据全部获取后一起返回

    def create_table(self):
        sql = """
            create table if not exists tx_work(
                id int primary key auto_increment,
                work_name varchar(100) not null,
                country_name varchar(50),
                city_name varchar(50),
                work_desc text
            );
        """

        try:
            self.cursor.execute(sql)
            print('表创建成功...')
        except Exception as e:
            print('表创建失败:', e)

    def insert_work_info(self, *args):
        """
        :param args:
        :return:
            id
            work_name
            country_name
            city_name
            work_desc
        """
        sql = """
            insert into tx_work() values (%s, %s, %s, %s, %s);
        """

        try:
            # args返回的数据类型是什么？
            self.cursor.execute(sql, args)
            self.db.commit()  # 需要手动提交后数据库才会保存数据: 事务
            print('数据插入成功:', args)
        except Exception as e:
            print('数据插入失败:', e)
            self.db.rollback()  # 回滚

    def main(self):
        self.create_table()
        all_work_generator_obj = self.get_work_info()
        for work_info_list in all_work_generator_obj:
            for work_info in work_info_list:
                work_name = work_info['RecruitPostName']
                country_name = work_info['CountryName']
                city_name = work_info['LocationName']
                work_desc = work_info['Responsibility']

                self.insert_work_info(0, work_name, country_name, city_name, work_desc)


if __name__ == '__main__':
    tx_work = TxWork()
    tx_work.main()
